﻿using System;
using System.Globalization;
using System.IO;
using Microsoft.Office.Interop.Excel;
using DataTable = System.Data.DataTable;

[assembly: CLSCompliant(true)]
namespace ContractManagementSystem.Common
{
    public static class CsvHelper
    {
        public static DataTable ReadFile(string fileName)
        {
            var table = new DataTable { Locale = CultureInfo.CurrentCulture };
            try
            {
                Application xlApp = new ApplicationClass();
                var xlWorkBook = xlApp.Workbooks.Open(fileName, 0, true, 5, "", "", true, XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
                var xlWorkSheet = (Worksheet)xlWorkBook.Worksheets.get_Item(1);

                var range = xlWorkSheet.UsedRange;

                for (int i = 1; i <= range.Rows.Count; i++)
                {
                    for (int j = 1; j <= range.Columns.Count; j++)
                    {
                        var range1 = range.Cells[i, j] as Range;
                        if (range1 != null)
                        {
                            var row = (string)range1.Value2;
                            if (i == 1 && j == 1)
                            {
                                var columns = row.Split(',');
                                foreach (var column in columns)
                                {
                                    table.Columns.Add(column);
                                }
                            }
                            else
                            {
                                var columns = row.Split(',');
                                table.Rows.Add(columns[0], columns[1], columns[2], columns[3], columns[4], columns[5],
                                    columns[6], columns[7], columns[8], columns[9], columns[10], columns[11],
                                    columns[12], columns[13], columns[14], columns[15], columns[16], columns[17],
                                    columns[18], columns[19], columns[20], columns[21], columns[22], columns[23],columns[24]);
                            }
                        }
                    }
                }
                xlWorkBook.Close(true, null, null);
                xlApp.Quit();

                //Marshal.ReleaseComObject(xlWorkSheet);
                //Marshal.ReleaseComObject(xlWorkBook);
                //Marshal.ReleaseComObject(xlApp);
            }
            catch (ExecutionEngineException)
            {
                return table;
            }
            return table;
        }

        public static DataTable ReadTextFile(string fileName)
        {
            var data = File.ReadAllLines(fileName);
            var table = new DataTable {Locale = CultureInfo.CurrentCulture};

            for (int i = 0; i < data.Length; i++)
            {
                if(i==0)
                {
                    var columns = data[i].Split(',');
                    foreach (var column in columns)
                    {
                        table.Columns.Add(column);
                    }
                }
                else
                {
                    var columns = data[i].Split(',');
                    table.Rows.Add(columns[0], columns[1], columns[2], columns[3], columns[4], columns[5],
                        columns[6], columns[7], columns[8], columns[9], columns[10], columns[11],
                        columns[12], columns[13], columns[14], columns[15], columns[16], columns[17],
                        columns[18], columns[19], columns[20], columns[21], columns[22], columns[23], columns[24]);
                }
            }

            return table;
        }

        public static bool WriteFile(DataTable table)
        {
            if (table == null || table.Rows.Count == 0) return false;
            var xlApp = new Application();

            var workbooks = xlApp.Workbooks;
            var workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);
            var worksheet = (Worksheet)workbook.Worksheets[1];
            
            for (int i = 0; i < table.Columns.Count; i++)
            {
                worksheet.Cells[1, i + 1] = table.Columns[i].ColumnName;
                var range = (Range)worksheet.Cells[1, i + 1];
                range.Interior.ColorIndex = 15;
                range.Font.Bold = true;
            }
            for (int r = 0; r < table.Rows.Count; r++)
            {
                for (int i = 0; i < table.Columns.Count; i++)
                {
                    worksheet.Cells[r + 2, i + 1] = table.Rows[r][i].ToString();
                }
            }
            xlApp.Visible = true;

            return true;
        }

    }
}
